How to Calculate the Number of Months Between Two Dates in Excel?

您所在的位置：网站首页 › Date Calculator Calcuate Number of Days Between Two Dates › How to Calculate the Number of Months Between Two Dates in Excel?

How to Calculate the Number of Months Between Two Dates in Excel?

2024-06-03 03:20| 来源: 网络整理| 查看: 265

How to Calculate the Number of Months Between Two Dates in Excel? by Steve Scott

Sometimes you may want to find out the number of months between two dates.

For example, if you are a project manager, you may want to find out how many months a certain project would take to complete when you know the start and the end date.

In this tutorial, you will learn five methods of calculating the number of months between two dates in Excel.

Note: Microsoft Excel stores date values as sequential serial numbers. 1 January 1900 is stored as the serial number 1, 15 January 1900 is serial number 15, and so on. This is what makes it possible for us to use dates in calculations.

Table of Contents

Toggle Method #1: DATEDIF function to Calculate Months Between Two Dates

The easiest way to calculate the total number of months between two dates is by using the DATEDIF function (called DATEDIF as it calculated the date difference).

Below I have a data set where I have the start date in column B and the end date in column C, and I want to calculate the number of months between these two dates in column D:

Below are the steps to get the number of months between the start and the end date:

Select cell D2 and type in the following formula: =DATEDIF(B2,C2,”m”) DATEDIF formula to get the months between two dates

Press the Enter key to get the result in cell D2. You can then copy the formula in all the remaining cells (you can do a simple copy-paste or you can use the fill handle and drag it down to copy the formula down the column). DATEDIF formula result

Once done, you will have the number of months between the two dates in column D

Explanation of the formula

=DATEDIF(B2,C2,”m”)

The formula uses the DATEDIF function, which uses an identifier as the last argument and can calculate the number of days, months, or years between two given dates.

This function is a legacy function retained in Excel for the purpose of compatibility with Lotus 1-2-3. Therefore, it does not show up as part of IntelliSense and it must be entered manually.

The syntax of the DATEDIF function:

DATEDIF(start_date,end_date,unit) The start_date is a required argument and represents the starting date of a given period. In our example, it represents the start date of the project. The end_date is a required argument and represents the last date of the period. In our example, it represents the last date of the project. The unit argument is required, and it represents the type of information you want to be returned. In our example, we wanted months and therefore used the “m” unit. You can use “y” for calculating years and “d” for calculating days

Note: This function only calculates completed months.

In case you want to get the number of months between a start date and the current date (today), use TODAY() instead of the end date cell reference. TODAY() function would automatically get the current date from your system’s settings.

Also read: How to Get Total Days in Month in Excel? Method #2: YEARFRAC and INT Functions to Calculate Months Between Two Dates

Another easy way to calculate the number of months between two dates is by using a combination of YEARFRAC and INT functions

Below I have the same data set where I have the start date and the end date and I want to calculate the total number of months between these two dates in column D:

Here are the steps to do this:

Select cell D2 and type in the formula below: =INT((YEARFRAC(B2,C2,1)*12)) INT and YEARFRAC formula

Press Enter to get the result. You can then double-click on the fill handle or drag it down to copy the formula down the column. INT and YEARFRAC formula to get months between two dates

INT and YEARFRAC formula to get months between two dates

Explanation of the formula

=INT((YEARFRAC(B2,C2,1)*12))

The formula uses the YEARFRAC and INT functions.

The YEARFRAC function takes the start date and the end date as the input arguments and returns a fraction of the year value (based on how many days were there between the start and the end dates)

The syntax of the YEARFRAC function:

YEARFRAC(start_date,end_date,[basis]) The start_date argument is mandatory and represents the starting date of a given period. In our example, it represents the start date of the project. The end_date is also a mandatory argument and represents the last date of the period. In our example, it represents the last date of the project. The basis is an optional argument, and it represents the type of day count basis to use. In our example, we used 1 which stands for actual/actual basis. If this argument is omitted, the 0 basis that stands for US (NASD) 30/360 is assumed.

The result of the YEARFRAC function is multiplied by 12 so that we get the total number of completed months.

INT function is then used to extract only the total number of completed months and truncates the decimal portion (which represents the part of the month, but not the complete month)

In case you don’t want only the completed months, and also want the partial month values (such as say 3.5 months), don’t use the INT function. So your formula would be =YEARFRAC(B2,C2,1)*12)

Also read: Calculate Days Left in the Year (Formula) Method #3: YEAR and MONTH Functions to Get Number Months Between Two Dates

Another easy and straightforward way to get the total number of months between two given dates is by using the YEAR and the MONTH function.

Below I have the dataset and want to calculate the total number of months elapsed between the two dates.

Use the below steps:

Select cell D2 and type in the following formula: =(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2) MONTH and YEAR formula to get months between two dates

Press Enter to get the result of the formula. Then you can copy the same formula for all the other cells in the column to get the result MONTH and YEAR formula result

Explanation of the formula

=(YEAR(C2)-YEAR(B2))*12+MONTH(C2)-MONTH(B2)

This formula uses the YEAR and MONTH functions.

The YEAR function returns the year of a date in the range 1900-9999.

The MONTH function returns the month of a date, a number from 1 (January) to 12 (December).

This formula subtracts the year of the starting date from the year of the ending date. The result is multiplied by 12 to convert it to months.

The product is then added to the difference between the month of the starting date and the month of the ending date. The result is the total number of months between the two dates.

Note: The difference between the month of the starting date and the month of the ending date can be a positive or negative number.

Also read: Count Days from a Date to Today in Excel (Formula) Method #4: Simple MONTH Formula to Get Number of Months Between Two Dates

Only use this method to calculate the number of months between dates that fall within the same year.

We use the following dataset that has dates that fall within the same year in our illustration.

We use the steps below:

Select cell D2 and type in the following formula: =MONTH(C2)-MONTH(B2) MONTH formula to get month between two dates

Press Enter to get the result. You can then double-click on the fill handle or drag it down to copy the formula down the column. MONTH formula result

Explanation of the formula

=MONTH(C2)-MONTH(B2)

The formula uses the MONTH function that returns the month of a given date. The month is returned as an integer ranging from 1 (January) to 12 (December).

Note: Excel stores dates as serial numbers.

Syntax of the MONTH function:

MONTH(serial_number)

The serial_number argument is a required argument. It represents the date of the month you want to find.

In our example, the MONTH function returns the month of the project’s start date and end date. The month of the start date is then subtracted from the month of the end date. The result is the total number of months between the two dates.

Also read: Convert Days to Months in Excel Method #5: Creating a User Defined Function (UDF) using VBA

If much of your work requires calculating the number of months between dates, creating a dedicated UDF for this task can save you time and effort.

We use the following dataset in our illustration:

We use the following steps:

In the active worksheet that contains the dataset, press Alt + F11 to open the Visual Basic Editor. Click Insert >> Module to insert a module.

Copy the following function procedure and paste it into the module 'Code by Steve from https://spreadsheetplanet.com Function TOTALMONTHS(startDate As Date, endDate As Date) As Integer 'Declaring the variables used in the code Dim startYear As Integer Dim endYear As Integer Dim startDay As Integer Dim endDay As Integer Dim startMonth As Integer Dim endMonth As Integer Dim monthDiff As Integer Dim yearDiff As Integer Dim monthAdjustment As Integer 'Setting values to variables based in the date startYear = Year(startDate) endYear = Year(endDate) startMonth = Month(startDate) endMonth = Month(endDate) startDay = Day(startDate) endDay = Day(endDate) monthDiff = endMonth - startMonth yearDiff = (endYear - startYear) * 12 'IF statement to check and adjust the month value If (monthDiff > 0 Or yearDiff > 0) Then If (startDay = 15) Then monthAdjustment = 1 ElseIf (startDay >= 15 And endDay

【本文地址】

公司简介

联系我们